package com.cn.lyl.dao.impl;

import com.cn.lyl.dao.NewsDao;
import com.cn.lyl.model.NewsModel;
import com.cn.lyl.util.JDBC;
import com.cn.lyl.util.Tool;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    @Override
    public int add(NewsModel newsModel) {
        String sql ="INSERT INTO news (title, content, author,face,count,create_time, update_time, enable, remark) VALUES"+
                "('"+ Tool.stringIsNotNull(newsModel.getTitle())+"','"+Tool.stringIsNotNull(newsModel.getContent())+"','"+
                Tool.stringIsNotNull(newsModel.getAuthor())+"','"+Tool.srcNotNull(newsModel.getFace())+"',0,now(),now(),1,'')";
        System.out.println("插入语句："+sql);
        return JDBC.update(sql);
    }

    @Override
    public int update(NewsModel newsModel) {
        String sql="update   news l  left join news_type_dependence d on l.id = d.news_id set ";
        StringBuffer stringBuffer = new StringBuffer();
        if(!"".equals(newsModel.getTitle())&&newsModel.getTitle()!=null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.title = '").append(newsModel.getTitle()).append("'");
        }
        if(!"".equals(newsModel.getFace())&&newsModel.getFace()!=null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.face = '").append(newsModel.getFace()).append("'");
        }


        if (!"".equals(newsModel.getContent())&&newsModel.getContent()!=null)
        {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.content = '").append(newsModel.getContent()).append("'");
        }
        if(!"".equals(newsModel.getAuthor())&&newsModel.getAuthor()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.author = '").append(newsModel.getAuthor()).append("'");
        }
        if(!"".equals(newsModel.getDependenceObj())&&newsModel.getDependenceObj()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append("d.type_id = (select id from news_type where type = '").append(newsModel.getDependenceObj()).append("')");
    }
        if(stringBuffer.length()>0){
            stringBuffer.append(", l.update_time = now()");
        }
        if(stringBuffer.length()>0){
            stringBuffer.append(",");
        }
        stringBuffer.append(" l.remark='' where l.id=").append(newsModel.getId());
        sql=sql+stringBuffer.toString();
        System.out.println("修改："+sql);
        return JDBC.update(sql);


    }

    @Override
    public int delete(NewsModel newsModel) {
        String sql="delete from news where id ="+newsModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public NewsModel findById(int id) {

        String sql ="select l.id ,l.is_home,l.face, l.title ,l.content,l.author,l.count,r.type from news l  left join news_type_dependence d on l.id = d.news_id left join news_type r on r.id = d.type_id where l.id= "+id;
        return setList(sql).size()>0?setList(sql).get(0):null;

    }

    @Override
    public List<NewsModel> findAll(NewsModel newsModel) {
        String sql ="select l.id ,l.is_home,l.face, l.title ,l.content,l.author,DATE_FORMAT(l.update_time,'%Y-%m-%d')as update_time,l.count,r.type from news l  left join news_type_dependence d on l.id = d.news_id left join news_type r on r.id = d.type_id where 1=1 ";
        String stringSql = commonSql(newsModel);
        int pageCurr=newsModel.getPage().getPageCurr();
        int pageSize=newsModel.getPage().getPageSize();
        stringSql +=" order by l.id desc limit "+(pageCurr-1)*pageSize+","+pageSize;
        sql = sql + stringSql;
        System.out.println(sql);
        return setList(sql);
    }

    @Override
    public NewsModel findNewest() {
        String sql ="SELECT id from news order by id desc ";
        NewsModel newsModel = new NewsModel();
        ResultSet query = JDBC.query(sql);
        try{
            if(query.next()){
                newsModel.setId(query.getInt("id"));
            }
        }catch (Exception e){

        }
        return newsModel;
    }

    @Override
    public int countFindAll(NewsModel newsModel) {
        String sql ="select count(l.id) count from news l  left join news_type_dependence d on l.id = d.news_id left join news_type r on r.id = d.type_id where 1=1 ";
        String stringSql = commonSql(newsModel);
        sql= sql+stringSql;
        ResultSet query = JDBC.query(sql);
        int size = 0;
        try {
            while (query.next()) {
                size = query.getInt("count");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return size;
    }



    private List<NewsModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<String> colList = new ArrayList<>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <=metaData.getColumnCount(); i++) {

                colList.add(metaData.getColumnLabel(i));

            }
        } catch (SQLException e) {
            return null;
        }
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                if (colList.contains("id")) {
                    model.setId(resultSet.getInt("id"));
                }
                if (colList.contains("title")) {
                    model.setTitle(resultSet.getString("title"));
                }
                if (colList.contains("content")) {
                    model.setContent(resultSet.getString("content"));
                }
                if (colList.contains("author")) {
                    model.setAuthor(resultSet.getString("author"));
                }
                if (colList.contains("face")) {
                    model.setFace(resultSet.getString("face"));
                }
                if (colList.contains("count")) {
                    model.setCount(resultSet.getInt("count"));
                }
                if (colList.contains("create_time")) {
                    model.setCreateTime(resultSet.getString("create_time"));
                }
                if (colList.contains("update_time")) {
                    model.setUpdateTime(resultSet.getString("update_time"));
                }
                if (colList.contains("enable")) {
                    model.setEnable(resultSet.getInt("enable"));
                }
                if(colList.contains("is_home")){
                    int num =resultSet.getInt("is_home");

                    if(num>0){
                        model.setIsHome("是");
                    }else {
                        model.setIsHome("否");
                    }
                }
                if (colList.contains("remark")) {
                    model.setRemark(resultSet.getString("remark"));
                }

                if(colList.contains("type")){
                    model.setDependenceObj(resultSet.getString("type"));
                }

                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    private String commonSql(NewsModel newsModel) {
        StringBuffer stringBuffer = new StringBuffer();
        if (newsModel.getTitle() != null && !"".equals(newsModel.getTitle())) {
            stringBuffer.append(" and l.title like'%").append(newsModel.getTitle()).append("%'");
        }
        if (newsModel.getAuthor() != null && !"".equals(newsModel.getAuthor())) {
            stringBuffer.append(" and l.author like '%").append(newsModel.getAuthor()).append("%'");
        }
        if(newsModel.getIsHome()!=null&&!"".equals(newsModel.getIsHome())&&!"全部".equals(newsModel.getIsHome())){
            if(newsModel.getIsHome().equals("是")){
                stringBuffer.append(" and l.is_home =").append(1);
            }else{
                stringBuffer.append(" and l.is_home =").append(0);
            }
        }
        if (newsModel.getCount() != null ) {
            stringBuffer.append(" and l.count > ").append(newsModel.getCount());
        }
        if ((newsModel.getStart() != null && !"".equals(newsModel.getStart())) && (newsModel.getEnd() != null && !"".equals(newsModel.getEnd()))) {
            stringBuffer.append(" and l.update_time between'").append(newsModel.getStart()).append("' and '").append(newsModel.getEnd()).append("'");
        }
        if ((newsModel.getStart() != null && !"".equals(newsModel.getStart())) && (newsModel.getEnd() == null || "".equals(newsModel.getEnd()))) {
            stringBuffer.append(" and l.update_time >='").append(newsModel.getStart()).append("'");
        }
        if ((newsModel.getStart() == null || "".equals(newsModel.getStart())) && (newsModel.getEnd() != null && !"".equals(newsModel.getEnd()))) {
            stringBuffer.append(" and l.update_time <='").append(newsModel.getEnd()).append("'");
        }



        if (newsModel.getDependenceObj() != null && !"全部".equals(newsModel.getDependenceObj() )&&!"".equals(newsModel.getDependenceObj())) {
            stringBuffer.append(" and r.type='").append((String) newsModel.getDependenceObj()).append("'");
        }


        return stringBuffer.toString();
    }

    @Override
    public int updateIsHome(NewsModel newsModel) {

        String sql = "update news set is_home = ABS(1-is_home) where id=" + newsModel.getId();
        System.out.println("修改："+sql);
        return JDBC.update(sql);
    }
}

